﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbDoc.Provider.Persistence;
using System.Configuration.Provider;
using System.Configuration;
using System.Data.SqlClient;
using DbDoc.BusinessObjects;

namespace DbDoc.Provider.Persistence.Sql
{
    public class SqlPersistenceProvider : PersistenceProvider
    {
        private String persistenceConnectionString;
        private List<string> includeDatabases = new List<string>();

        #region Properties
        public override string ConnectionInfo
        {
            get { return persistenceConnectionString; }
        }

        public override string StorageName
        {
            get
            {
                if (string.IsNullOrWhiteSpace(ConnectionInfo))
                    return string.Empty;
                string[] settings = ConnectionInfo.Split(new char[] { ';' });

                var database = (from s in settings
                                where s.Split(new char[] { '=' })[0].ToLower() == "initial catalog"
                                select s.Split(new char[] { '=' })[1]).FirstOrDefault();
                var server = (from s in settings
                              where s.Split(new char[] { '=' })[0].ToLower() == "server"
                              select s.Split(new char[] { '=' })[1]).FirstOrDefault();
                return string.Format("{0} - {1}", server, database);

            }
        }

        public override string Name
        {
            get { return "Sql Provider"; }
        }
        #endregion

        #region Persistence Provider implementation

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if ((config == null) || (config.Count == 0))
                throw new ArgumentNullException("You must supply a valid configuration dictionary.");

            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "SQL Server 2008 provider.");
            }

            //Let ProviderBase perform the basic initialization
            base.Initialize(name, config);

            //Perform feature-specific provider initialization here

            //Get the connection string
            string persistenceConnection = config["connectionString"];
            if (String.IsNullOrEmpty(persistenceConnection))
                throw new ProviderException("You must specify a connectionStringName attribute.");


            ConnectionStringsSection cs =
                (ConnectionStringsSection)ConfigurationManager.GetSection("connectionStrings");
            if (cs == null)
                throw new ProviderException("An error occurred retrieving the connection strings section.");

            if (cs.ConnectionStrings[persistenceConnection] == null)
                throw new ProviderException("The connection string could not be found in the connection strings section.");
            else
                persistenceConnectionString = cs.ConnectionStrings[persistenceConnection].ConnectionString;


            if (String.IsNullOrEmpty(persistenceConnectionString))
                throw new ProviderException("The connection string is invalid.");
            config.Remove("connectionString");


            //Check to see if unexpected attributes were set in configuration
            if (config.Count > 0)
            {
                string extraAttribute = config.GetKey(0);
                if (!String.IsNullOrEmpty(extraAttribute))
                    throw new ProviderException("The following unrecognized attribute was found in " + Name + "'s configuration: '" +
                                                extraAttribute + "'");
                else
                    throw new ProviderException("An unrecognized attribute was found in the provider's configuration.");
            }
        }

        public override IList<DbDoc.BusinessObjects.Database> GetDatabases()
        {
            List<DbDoc.BusinessObjects.Database> databases = new List<DbDoc.BusinessObjects.Database>();
            SqlConnection con = new SqlConnection(persistenceConnectionString);
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = string.Format("SELECT D.Id, ParentId, Name, Description, Deleted , C.Id CommentId, C.Message, C.CreatedBy, C.CreatedOn, C.ModifiedBy, C.ModifiedOn FROM DbObjects D LEFT JOIN  Comment C ON	D.Id = C.DbObjectId WHERE DbType = {0} ", (int)DbType.Database);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DbDoc.BusinessObjects.Database db = new DbDoc.BusinessObjects.Database();
                PopulateDbObject(reader, db);
                databases.Add(db);
            }
            reader.Close();
            con.Close();

            return databases;
        }

        public override IList<DbDoc.BusinessObjects.Table> GetTables(DbDoc.BusinessObjects.Database db)
        {
            List<DbDoc.BusinessObjects.Table> tables = new List<DbDoc.BusinessObjects.Table>();
            SqlConnection con = new SqlConnection(persistenceConnectionString);
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = string.Format("SELECT D.Id, ParentId, Name, Description, Deleted , C.Id CommentId, C.Message, C.CreatedBy, C.CreatedOn, C.ModifiedBy, C.ModifiedOn FROM DbObjects D LEFT JOIN  Comment C ON	D.Id = C.DbObjectId WHERE DbType = {0} AND ParentId = {1} ", (int)DbType.Table, db.Id);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DbDoc.BusinessObjects.Table tbl = new DbDoc.BusinessObjects.Table();
                PopulateDbObject(reader, tbl);
                tables.Add(tbl);
            }
            reader.Close();
            con.Close();

            return tables;
        }

        public override IList<DbDoc.BusinessObjects.View> GetViews(DbDoc.BusinessObjects.Database db)
        {
            List<DbDoc.BusinessObjects.View> views = new List<DbDoc.BusinessObjects.View>();
            SqlConnection con = new SqlConnection(persistenceConnectionString);
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = string.Format("SELECT D.Id, ParentId, Name, Description, Deleted , C.Id CommentId, C.Message, C.CreatedBy, C.CreatedOn, C.ModifiedBy, C.ModifiedOn FROM DbObjects D LEFT JOIN  Comment C ON	D.Id = C.DbObjectId WHERE DbType = {0} AND ParentId = {1} ", (int)DbType.View, db.Id);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DbDoc.BusinessObjects.View vw = new DbDoc.BusinessObjects.View();
                PopulateDbObject(reader, vw);
                views.Add(vw);
            }
            reader.Close();
            con.Close();

            return views;
        }

        public override IList<StoredProc> GetStoredProcs(DbDoc.BusinessObjects.Database db)
        {
            List<DbDoc.BusinessObjects.StoredProc> procs = new List<DbDoc.BusinessObjects.StoredProc>();
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT D.Id, ParentId, Name, Description, Deleted , C.Id CommentId, C.Message, C.CreatedBy, C.CreatedOn, C.ModifiedBy, C.ModifiedOn FROM DbObjects D LEFT JOIN  Comment C ON	D.Id = C.DbObjectId WHERE DbType = {0} AND ParentId = {1} ", (int)DbType.StoredProc, db.Id);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DbDoc.BusinessObjects.StoredProc proc = new DbDoc.BusinessObjects.StoredProc();
                    PopulateDbObject(reader, proc);
                    procs.Add(proc);
                }
                reader.Close();
                con.Close();

                return procs;
            }
        }

        public override IList<DbDoc.BusinessObjects.Column> GetColumns(DbDoc.BusinessObjects.Database db, DbDoc.BusinessObjects.Table tbl)
        {
            List<DbDoc.BusinessObjects.Column> columns = new List<DbDoc.BusinessObjects.Column>();
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT D.Id, ParentId, Name, Description, Deleted , C.Id CommentId, C.Message, C.CreatedBy, C.CreatedOn, C.ModifiedBy, C.ModifiedOn FROM DbObjects D LEFT JOIN  Comment C ON	D.Id = C.DbObjectId WHERE DbType = {0} AND ParentId = {1} ", (int)DbType.Column, tbl.Id);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DbDoc.BusinessObjects.Column col = new DbDoc.BusinessObjects.Column();
                    PopulateDbObject(reader, col);
                    columns.Add(col);
                }
                reader.Close();
                con.Close();

                return columns;
            }
        }

        public override void SaveDbObject(DbObject db)
        {
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("INSERT INTO DbObjects(Name,Description, Deleted, DbType, ParentId) VALUES (@name, @description, @deleted, @dbtype, @parentId)");
                SqlParameter pParentId = new SqlParameter();
                pParentId.ParameterName = "@parentId";
                if (null != db.Parent)
                    pParentId.Value = db.Parent.Id;
                else
                    pParentId.Value = DBNull.Value;

                cmd.Parameters.Add(pParentId);
                cmd.Parameters.AddWithValue("@name", db.Name);
                cmd.Parameters.AddWithValue("@description", string.Empty);
                cmd.Parameters.AddWithValue("@deleted", db.Deleted);
                cmd.Parameters.AddWithValue("@dbtype", db.DbType);
                con.Open();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT Id FROM DbObjects WHERE NAME = @name and DbType = @dbtype And ISNULL(ParentId ,-1) = ISNULL(@parentId,-1)";
                db.Id = Int32.Parse(cmd.ExecuteScalar().ToString());
                con.Close();
            }
        }

        public override void UpdateDbObject(DbObject db)
        {
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                con.Open();
                cmd.CommandText = "SELECT Id FROM Comment WHERE DbObjectId = @dbobjectId";
                cmd.Parameters.AddWithValue("@dbobjectId", db.Id);
                cmd.Parameters.AddWithValue("@message", db.Comment.Text);

                cmd.Parameters.AddWithValue("@modifiedBy", db.Comment.ModifiedBy);
                cmd.Parameters.AddWithValue("@modifiedOn", db.Comment.ModifiedOn);
                cmd.Parameters.AddWithValue("@createdOn", db.Comment.CreatedOn);
                cmd.Parameters.AddWithValue("@createdBy", db.Comment.CreatedBy);

                int commentId;
                if (null != cmd.ExecuteScalar())
                    Int32.TryParse(cmd.ExecuteScalar().ToString(), out commentId);
                else
                    commentId = 0;
                cmd.Parameters.AddWithValue("@commentId", commentId);
                if (commentId > 0)
                {
                    cmd.CommandText = string.Format("INSERT INTO CommentLog (ParentId, DbObjectId, Message, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn) SELECT Id, DbObjectId, Message, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn FROM Comment WHERE Id = @commentId");
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "UPDATE Comment SET Message = @message, ModifiedBy = @modifiedBy, ModifiedOn = @modifiedOn WHERE DbObjectId = @dbobjectId";
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    cmd.CommandText = "INSERT INTO Comment (DbObjectId, Message) VALUES (@dbobjectId, @message)";
                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }
        }

        public void UpdateDbObjectOnly(DbObject db)
        {
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                con.Open();
                cmd.Parameters.AddWithValue("@dbobjectId", db.Id);
                cmd.Parameters.AddWithValue("@deleted", db.Deleted);
                cmd.CommandText = "UPDATE DbObjects SET Deleted  = @deleted WHERE Id = @dbobjectId";
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        public override IEnumerable<Comment> GetCommentsHistory(DbObject obj)
        {
            List<Comment> comments = new List<Comment>();
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("select Id,  Message, DbObjectId, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn From CommentLog WHERE DbObjectId = {0} ORDER BY ModifiedOn DESC ", obj.Id);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    comments.Add(new Comment()
                    {
                        Id = (int)reader["Id"],
                        Text = (string)reader["Message"],
                        CreatedBy = (string)reader["CreatedBy"],
                        CreatedOn = (DateTime)reader["CreatedOn"],
                        ModifiedBy = (string)reader["ModifiedBy"],
                        ModifiedOn = (DateTime)reader["ModifiedOn"]
                    });

                }
                reader.Close();
                con.Close();

                return comments;
            }
        }

        public override bool ValidateUser(string username, string password)
        {
            bool valid = false;
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT COUNT(*) FROM AppUser WHERE Username = '{0}' AND Password = '{1}'", username, password);
                con.Open();
                if ((Int32.Parse(cmd.ExecuteScalar().ToString())) == 1)
                    valid = true;
                con.Close();

                return valid;
            }
        }

        public override bool ValidateUser(AppUser user)
        {
            return ValidateUser(user.Username, user.Password);
        }

        public override AppUser GetUserByUsername(string username)
        {
            AppUser appUser = null;
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM AppUser WHERE Username = '{0}'", username);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    appUser = new AppUser();
                    appUser.Id = reader.GetInt32(0);
                    appUser.Username = reader.GetString(1);
                    appUser.Password = reader.GetString(2);
                    appUser.IsAdmin = reader.GetBoolean(3);
                }
                con.Close();
                return appUser;
            }
        }

        public override AppUser Login(string username, string password)
        {
            AppUser appUser = null;
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM AppUser WHERE Username = '{0}' AND Password = '{1}'", username, password);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    appUser = new AppUser();
                    appUser.Id = reader.GetInt32(0);
                    appUser.Username = reader.GetString(1);
                    appUser.Password = reader.GetString(2);
                    appUser.IsAdmin = reader.GetBoolean(3);
                }
                con.Close();
                return appUser;
            }
        }

        public override IList<AppUser> GetAllUsers()
        {
            IList<AppUser> users = new List<AppUser>();
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM AppUser");
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    AppUser appUser = new AppUser();
                    appUser.Id = reader.GetInt32(0);
                    appUser.Username = reader.GetString(1);
                    appUser.Password = reader.GetString(2);
                    appUser.IsAdmin = reader.GetBoolean(3);
                    users.Add(appUser);
                }
                con.Close();
                return users;
            }
        }

        public override Setting GetByKey(string key)
        {
            Setting setting = null;
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM [AppSettings] WHERE [Key] = '{0}' ", key);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    setting = new Setting();
                    setting.Id = reader.GetInt32(0);
                    setting.Key = reader.GetString(1);
                    setting.RawValue = reader.GetString(2);
                    setting.DataType = reader.GetString(3);
                }
                con.Close();
                return setting;
            }
        }

        public override IList<Setting> GetAll()
        {
            List<Setting> settings = new List<Setting>();
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM [AppSettings] ");
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Setting setting = new Setting();
                    setting.Id = reader.GetInt32(0);
                    setting.Key = reader.GetString(1);
                    setting.RawValue = reader.GetString(2);
                    setting.DataType = reader.GetString(3);
                    settings.Add(setting);
                }
                con.Close();
                return settings;
            }
        }

        public override void SaveSetting(Setting input)
        {
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT Count(*) FROM [AppSettings] WHERE [Key] = '{0}'", input.Key);
                con.Open();
                if (Convert.ToInt32(cmd.ExecuteScalar().ToString()) == 0)
                {
                    cmd.CommandText = string.Format("INSERT INTO [AppSettings] ([Key], [Value], DataType) VALUES ('{0}', '{1}', '{2}')", input.Key, input.RawValue, input.DataType);
                }
                else
                {
                    cmd.CommandText = string.Format("UPDATE [AppSettings] SET [Value] = '{0}' WHERE [Key] = '{1}'", input.RawValue, input.Key);
                }
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        public override bool TestConnection()
        {
            return true;
        }

        public override void SaveUser(AppUser user)
        {
            using (SqlConnection con = new SqlConnection(persistenceConnectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT Count(1) FROM [AppUser] WHERE [Username] = '{0}'", user.Username);
                con.Open();
                if (Convert.ToInt32(cmd.ExecuteScalar().ToString()) == 0)
                {
                    cmd.CommandText = string.Format("INSERT INTO [AppUser] ([Username], [Password], IsAdmin) VALUES ('{0}', '{1}', {2})", user.Username, user.Password, user.IsAdmin?1:0);
                }
                else
                {
                    cmd.CommandText = string.Format("UPDATE [AppUser] SET [Password] = '{0}', IsAdmin = {1} WHERE [Username] = '{2}'", user.Password, user.IsAdmin? 1:0, user.Username);
                }
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        #endregion

        #region Private Methods
        private static void PopulateDbObject(SqlDataReader reader, DbDoc.BusinessObjects.DbObject obj)
        {
            obj.Id = reader.GetInt32(reader.GetOrdinal("Id"));
            obj.Deleted = reader.GetBoolean(reader.GetOrdinal("Deleted"));
            obj.Name = reader.GetString(reader.GetOrdinal("Name"));
            obj.Description = reader.GetString(reader.GetOrdinal("Description"));

            if (!reader.IsDBNull(reader.GetOrdinal("CommentId")))
                obj.Comment.Id = reader.GetInt32(reader.GetOrdinal("CommentId"));
            if (!reader.IsDBNull(reader.GetOrdinal("Message")))
                obj.Comment.Text = reader.GetString(reader.GetOrdinal("Message"));
            if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy")))
                obj.Comment.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
            if (!reader.IsDBNull(reader.GetOrdinal("CreatedOn")))
                obj.Comment.CreatedOn = reader.GetDateTime(reader.GetOrdinal("CreatedOn"));
            if (!reader.IsDBNull(reader.GetOrdinal("ModifiedBy")))
                obj.Comment.ModifiedBy = reader.GetString(reader.GetOrdinal("ModifiedBy"));
            if (!reader.IsDBNull(reader.GetOrdinal("ModifiedOn")))
                obj.Comment.ModifiedOn = reader.GetDateTime(reader.GetOrdinal("ModifiedOn"));
        }

        #endregion


    }
}
